package com.dao.impl;

import com.bean.Hw;
import com.bean.HwStu;
import com.bean.Teacher;
import com.dao.BaseDao;
import com.dao.HwDao;
import com.utils.SqlUtils;

import java.util.List;
import java.util.Map;

public class HwDaoImpl extends BaseDao implements HwDao {
    String[] strings = {"TeacherName","HwAttachUrl","HwAttachTitle"};

    @Override
    public List<Hw> selectForPageItems(int begin, int pageSize, String content) {
        if (content == null) {
            content = "";
        }
        content = "%" + content + "%";
        String sql = SqlUtils.pageSql(Hw.class, Teacher.class, strings);
        return queryForList(Hw.class, sql, content, begin, pageSize);
    }

    @Override
    public Integer selectForPageTotalCount(String content) {
        if (content == null) {
            content = "";
        }
        content = "%" + content + "%";
        String sql = SqlUtils.countSql(Hw.class, Teacher.class, strings);
        Number count = (Number) queryForSingleValue(sql, content);
        return count.intValue();
    }

    @Override
    public Hw selectBy(Hw hw) {
        Map.Entry<String, Object[]> entry = SqlUtils.selectBy(hw, Teacher.class, strings);
        return queryForOne(Hw.class, entry.getKey(), entry.getValue());
    }

    @Override
    public int insert(Hw hw) {
        Map.Entry<String, Object[]> entry = SqlUtils.insertSql(hw, strings);
        return update(entry.getKey(), entry.getValue());
    }

    @Override
    public int deleteByPrimaryKey(Integer id) {
        String sql = "delete from `Hw` where `id` = ?";
        return update(sql, id);
    }

    @Override
    public int updateByPrimaryKey(Hw hw) {
        Map.Entry<String, Object[]> entry = SqlUtils.updateSql(hw, strings);
        return update(entry.getKey(), entry.getValue());
    }

    @Override
    public List<Hw> hwNoSubmit(int id, int begin, int pageSize) {
        String sql = "select hw.`id`,hw.`teacherId`,hw.`title`,hw.`describe`,hw.`endTime`,hwAttach.`url` hwAttachUrl,hwAttach.`title` hwAttachTitle,teacher.`name` teacherName from hw " +
                "left join hwAttach on hw.id=hwAttach.hwId " +
                "left join Teacher on hw.TeacherId=Teacher.id " +
                "where hw.`id` not in(select `hwId` from hwStu where `studentId`=?) and hw.`endTime` > now() order by hw.`endTime` desc LIMIT ?,?";
        return queryForList(Hw.class, sql, id, begin, pageSize);
    }

    @Override
    public Integer hwNoSubmitCount(int id) {
        String sql = "select count(1) from hw " +
                "left join hwAttach on hw.id=hwAttach.hwId " +
                "where hw.id not in(select `hwId` from hwStu where `studentId`=?) and hw.`endTime` > now();";
        Number count = (Number) queryForSingleValue(sql, id);
        return count.intValue();
    }

    @Override
    public List<Hw> hwSubmit(int id, int begin, int pageSize) {
        String sql = "select hw.`id`,hw.`teacherId`,hw.`title`,hw.`describe`,hw.`endTime`,teacher.`name` teacherName from hw " +
                "left join Teacher on hw.TeacherId=Teacher.id " +
                "where hw.`id` in(select `hwId` from hwStu where `studentId`=?) order by hw.`endTime` desc LIMIT ?,?";
        return queryForList(Hw.class, sql, id, begin, pageSize);
    }

    @Override
    public Integer hwSubmitCount(int id) {
        String sql = "select count(1) from hw " +
                "where hw.`id` in(select hwId from hwStu where studentId=?)";
        Number count = (Number) queryForSingleValue(sql, id);
        return count.intValue();
    }

    @Override
    public List<Hw> hwOverdue(int id, int begin, int pageSize) {
        String sql = "select hw.`id`,hw.`teacherId`,hw.`title`,hw.`describe`,hw.`endTime`,teacher.`name` teacherName from hw " +
                "left join Teacher on hw.TeacherId=Teacher.id " +
                "where hw.`id` not in(select hwId from hwStu where studentId=?) and endTime < now() order by hw.`endTime` desc LIMIT ?,?";
        return queryForList(Hw.class, sql, id, begin, pageSize);
    }

    @Override
    public Integer hwOverdueCount(int id) {
        String sql = "select count(1) from hw " +
                "where id not in(select hwId from hwStu where studentId=?)";
        Number count = (Number) queryForSingleValue(sql, id);
        return count.intValue();
    }

    @Override
    public List<Hw> hwOverTime(int id, int begin, int pageSize) {
        String sql="SELECT hw.`id`,hw.title,hw.`describe`,hw.endTime,teacher.`name` teacherName ,hw.endTime FROM hw " +
                "LEFT JOIN teacher ON teacher.id=hw.teacherId WHERE teacher.id=? and hw.`endTime`< now() order by hw.`endTime` desc LIMIT ?,?";
        return queryForList(Hw.class, sql, id, begin, pageSize);
    }

    @Override
    public Integer hwOverTimeCount(int id) {
        String sql = "select count(1) from hw " +
                "LEFT JOIN teacher ON teacher.id=hw.teacherId WHERE teacher.id=? and hw.`endTime`< now()";
        Number count = (Number) queryForSingleValue(sql, id);
        return count.intValue();
    }
    @Override
    public List<HwStu> notComment(int id, int begin, int pageSize) {
        String sql="select hwstu.`id`,hwstu.`status`,student.`name` studentName ,class.`name` className ,hw.`title`,hwstuattach.`url` hWStuAttachUrl ,hwstu.`submitTime` from hwStu " +
                "left join hw on hwStu.hwId=hw.Id " +
                "left join student on student.`id`=hwstu.studentId " +
                "LEFT JOIN class ON class.id=student.classId " +
                "LEFT JOIN hwstuattach on hwstu.id=hwstuattach.hwStuId " +
                "WHERE hw.id=? and hwStu.`comment` is null order by hw.endTime desc LIMIT ?,?";
        return queryForList(HwStu.class, sql, id, begin, pageSize);
    }

    @Override
    public Integer notCommentCount(int id) {
        String sql = "select count(1) from hwStu " +
                "left join hw on hwStu.hwId=hw.Id " +
                "WHERE hw.id=? and hwStu.`comment` is null";
        Number count = (Number) queryForSingleValue(sql, id);
        return count.intValue();
    }

    @Override
    public List<HwStu> openComment(int id, int begin, int pageSize) {
        String sql="select hwstu.`status`,hwStu.`comment`,hwStu.`commentTime`,student.`name` studentName ,class.`name` className,hwstu.`id`,hwstu.`submitTime`,hwstuattach.`url` hWStuAttachUrl ,hwstu.hwId from hwStu " +
                "left join hw on hwStu.hwId=hw.Id " +
                "left join student on student.`id`=hwstu.studentId " +
                "LEFT JOIN class ON class.id=student.classId " +
                "LEFT JOIN hwstuattach on hwstu.id=hwstuattach.hwStuId " +
                "WHERE hw.id=? and hwStu.`comment` is not null order by hw.endTime desc LIMIT ?,?";
        return queryForList(HwStu.class, sql, id, begin, pageSize);
    }

    @Override
    public Integer openCommentCount(int id) {
        String sql="select count(1) from hwStu  " +
                " left join hw on hwStu.hwId=hw.Id  "+
                "WHERE hw.id=? and hwStu.`comment` is not null";
        Number count = (Number) queryForSingleValue(sql, id);
        return count.intValue();
    }

    @Override
    public List<Hw> myPage(int id, int begin, int pageSize) {
        String sql="SELECT  hw.`id`,hw.title,hw.`describe`,hw.endTime,teacher.`name`teacherName FROM hw " +
                "LEFT JOIN teacher on hw.teacherId=teacher.id WHERE teacher.id=? order by hw.endTime desc LIMIT ?,?";
        return queryForList(Hw.class, sql, id, begin, pageSize);
    }

    @Override
    public Integer myPageCount(int id) {
        String sql="SELECT count(1) FROM hw LEFT JOIN teacher on hw.teacherId=teacher.id WHERE teacher.id=?";
        Number count = (Number) queryForSingleValue(sql, id);
        return count.intValue();
    }
}
